const sql = require('mssql');
/**
 * 
 * 入库价格差异查询
 */

const getSystemStoragePriceDiff = async (ctx, next) => {
    let getBody = ctx.request.body;
    let pageNum = getBody.pageNum;
    let searchVal = getBody.searchVal;
    let totalSql = `SELECT COUNT(T1.YPBM) AS TOTAL FROM ( SELECT RM.YPBM FROM RKD_MX RM 
    LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN DHD_MX DM ON RZ.ID_DHD = DM.ID AND RM.YPBM = DM.YPBM LEFT JOIN DHD_ZB DZ ON RZ.ID_DHD = DZ.ID 
    LEFT JOIN BM_YP Y ON RM.YPBM = Y.BM  WHERE (DM.DJ - RM.DJ) != 0 `;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY RZ.ID DESC) AS px,RZ.DWBM,RZ.DWMC,DZ.RQ AS DDRQ,DZ.ID AS DDID,RZ.RQ AS RKRQ,
    RZ.ID AS RKID,RM.YPBM,RM.YPMC,RM.YPGG,RM.SCCJ,RM.PZWH,RM.DJ AS RKDJ,DM.DJ AS DDDJ,(DM.DJ - RM.DJ) AS CYDJ,T1.PJDJ,T1.ZDDJ,T1.ZGDJ FROM RKD_MX RM 
    LEFT JOIN (SELECT AVG(DJ) AS PJDJ,MIN(DJ) AS ZDDJ,MAX(DJ) AS ZGDJ,YPBM FROM RKD_MX  GROUP BY YPBM) T1 ON RM.YPBM = T1.YPBM
    LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN DHD_MX DM ON RZ.ID_DHD = DM.ID AND RM.YPBM = DM.YPBM LEFT JOIN DHD_ZB DZ ON RZ.ID_DHD = DZ.ID 
    LEFT JOIN BM_YP Y ON RM.YPBM = Y.BM WHERE (DM.DJ - RM.DJ) != 0 `;
    if (searchVal.GYSMC) {
        startSql += ` AND RZ.DWMC LIKE '%${searchVal.GYSMC}%'`;
        totalSql += ` AND RZ.DWMC LIKE '%${searchVal.GYSMC}%'`;
    }
    if (searchVal.id) {
        // let whereCount = startSql.split('WHERE').length - 1;
        // startSql += whereCount <= 1 ? 'WHERE' : 'AND';
        startSql += ` AND (RM.ID = '${searchVal.id}' OR RZ.ID_DHD = '${searchVal.id}')`;
        // totalSql += whereCount <= 1 ? 'WHERE' : 'AND';
        totalSql += ` AND (RM.ID = '${searchVal.id}' OR RZ.ID_DHD = '${searchVal.id}')`;
    }
    if (searchVal.YPBM) {
        // let whereCount = startSql.split('WHERE').length - 1;
        // startSql += whereCount <= 1 ? 'WHERE' : 'AND';
        startSql += ` AND (RM.YPBM = '${searchVal.YPBM}' OR Y.JP LIKE '%${searchVal.YPBM}%')`;
        // totalSql += whereCount <= 1 ? 'WHERE' : 'AND';
        totalSql += ` AND (RM.YPBM = '${searchVal.YPBM}' OR Y.JP LIKE '%${searchVal.YPBM}%')`;
    }
    if(0 in searchVal.time){
        // let whereCount = startSql.split('WHERE').length - 1;
        // startSql += whereCount <= 1 ? 'WHERE' : 'AND';
        startSql += ` AND (RZ.RQ >= '${searchVal.time[0]}' AND RZ.RQ <= '${searchVal.time[1]}')`;
        // totalSql += whereCount <= 1 ? 'WHERE' : 'AND';
        totalSql += ` AND (RZ.RQ >= '${searchVal.time[0]}' AND RZ.RQ <= '${searchVal.time[1]}')`;
    }
    startSql += ` GROUP BY RZ.DWBM,RZ.DWMC,DZ.RQ,DZ.ID,RZ.RQ,RZ.ID,RM.YPBM,RM.YPGG,RM.SCCJ,RM.PZWH,RM.DJ ,DM.DJ,RM.YPMC,T1.PJDJ,T1.ZDDJ,T1.ZGDJ) AS t2 WHERE t2.px between ${(pageNum - 1) * 10 + 1} and ${pageNum * 10} `
    totalSql += ` GROUP BY RZ.DWBM,RZ.DWMC,DZ.RQ,DZ.ID,RZ.RQ,RZ.ID,RM.YPBM,RM.YPGG,RM.SCCJ,RM.PZWH,RM.DJ ,DM.DJ,RM.YPMC) T1 `;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = { total: total.recordset[0].TOTAL, result: result.recordset }
}


module.exports = {
    'POST /getSystemStoragePriceDiff': getSystemStoragePriceDiff
}
